IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[Nurse]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
   DROP TABLE [dbo].[Nurse]
GO

/*----------------------------------------------------------------------
'Created By : Preeti
'Created On : 10/18/2008
'SP Name    : dbo.SP_SetNurse
'Used In    : 
'------------------------------------------------------------------------
'Modification Log  
'------------------------------------------------------------------------
'Created By        Modified By         Remarks                             
'-----------       ------------        ----------------------------------
'                                                                          
'----------------------------------------------------------------------*/

CREATE TABLE [dbo].[Nurse] (
   NurseID   Varchar(30),
   [FullName]     Varchar(50),
   [Address]     Varchar(50),
   [City]     Varchar(50),
   [State]     Varchar(50),
   [ZipCode]     Varchar(50),
   [MobilePhone]     Varchar(50),
   [OfficePhone]     Varchar(50),
   [HomePhone]     Varchar(50),
   [EmailAddress]     Varchar(50),
   [EmergencyContactName]     Varchar(50),
   [EmergencyContactPhone]     Varchar(50),
   [Category]     Varchar(50)
)
GO

GRANT SELECT,INSERT,DELETE,UPDATE ON dbo.Nurse TO hms_usr
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id =
object_id(N'dbo.SP_GetNurse') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
   DROP PROCEDURE dbo.SP_GetNurse
GO


/*----------------------------------------------------------------------
'Created By : Preeti
'Created On : 10/18/2008
'SP Name    : dbo.SP_GetNurse
'Used In    : 
'------------------------------------------------------------------------
'Modification Log  
'------------------------------------------------------------------------
'Created By        Modified By         Remarks                             
'-----------       ------------        ----------------------------------
'                                                                          
'----------------------------------------------------------------------*/

CREATE PROCEDURE dbo.SP_GetNurse
@vchNurseID VARCHAR(30)
AS
   SELECT
       NurseID,
       FullName,
       Address,
       City,
       State,
       ZipCode,
       MobilePhone,
       OfficePhone,
       HomePhone,
       EmailAddress,
       EmergencyContactName,
       EmergencyContactPhone,
       Category
   FROM Nurse
   WHERE NurseID=@vchNurseID
GO

GRANT EXEC ON dbo.SP_GetNurse TO hms_usr
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id =
object_id(N'dbo.SP_SetNurse') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
   DROP PROCEDURE dbo.SP_SetNurse
GO


/*----------------------------------------------------------------------
'Created By : Preeti
'Created On : 10/18/2008
'SP Name    : dbo.SP_SetNurse
'Used In    : 
'------------------------------------------------------------------------
'Modification Log  
'------------------------------------------------------------------------
'Created By        Modified By         Remarks                             
'-----------       ------------        ----------------------------------
'                                                                          
'----------------------------------------------------------------------*/

CREATE PROCEDURE dbo.SP_SetNurse
@vchNurseID Varchar(30),
@vchFullName    Varchar(50),
@vchAddress    Varchar(50),
@vchCity    Varchar(50),
@vchState    Varchar(50),
@vchZipCode    Varchar(50),
@vchMobilePhone    Varchar(50),
@vchOfficePhone    Varchar(50),
@vchHomePhone    Varchar(50),
@vchEmailAddress    Varchar(50),
@vchEmergencyContactName    Varchar(50),
@vchEmergencyContactPhone    Varchar(50),
@vchCategory    Varchar(50)
AS
DECLARE  @iRetValue     integer
   IF NOT EXISTS(SELECT     NurseID
   FROM Nurse
   WHERE   (NurseID = @vchNurseID)
   )

   BEGIN
   INSERT INTO Nurse(
       NurseID,
       FullName,
       Address,
       City,
       State,
       ZipCode,
       MobilePhone,
       OfficePhone,
       HomePhone,
       EmailAddress,
       EmergencyContactName,
       EmergencyContactPhone,
       Category
  )
   VALUES (
      @vchNurseID,
       @vchFullName,
       @vchAddress,
       @vchCity,
       @vchState,
       @vchZipCode,
       @vchMobilePhone,
       @vchOfficePhone,
       @vchHomePhone,
       @vchEmailAddress,
       @vchEmergencyContactName,
       @vchEmergencyContactPhone,
       @vchCategory
   )
   END
ELSE  IF EXISTS(SELECT     NurseID
   FROM Nurse
   WHERE   (NurseID = @vchNurseID)
   )

   BEGIN
   UPDATE Nurse
   SET
       FullName = @vchFullName,
       Address = @vchAddress,
       City = @vchCity,
       State = @vchState,
       ZipCode = @vchZipCode,
       MobilePhone = @vchMobilePhone,
       OfficePhone = @vchOfficePhone,
       HomePhone = @vchHomePhone,
       EmailAddress = @vchEmailAddress,
       EmergencyContactName = @vchEmergencyContactName,
       EmergencyContactPhone = @vchEmergencyContactPhone,
       Category = @vchCategory
  WHERE (NurseID = @vchNurseID)
   END

IF (@@ERROR <> 0)
   BEGIN
       declare @SPErrMsg varchar(800)
       SET @SPErrMsg = 'Error: Error in Stored procedure dbo.SP_SetNurse for Loan# : ' + CAST (@vchNurseID as varchar)
       RAISERROR (@SPErrMsg,16,1)
       Select @iRetValue = -1
   END
ELSE
   BEGIN
       Select @iRetValue = 0
   END
   
   RETURN @iRetValue
GO

GRANT EXEC ON dbo.SP_SetNurse TO hms_usr
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id =
object_id(N'dbo.SP_DeleteNurse') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
   DROP PROCEDURE dbo.SP_DeleteNurse
GO


/*----------------------------------------------------------------------
'Created By : Preeti
'Created On : 10/18/2008
'SP Name    : dbo.SP_DeleteNurse
'Used In    : 
'------------------------------------------------------------------------
'Modification Log  
'------------------------------------------------------------------------
'Created By        Modified By         Remarks                             
'-----------       ------------        ----------------------------------
'                                                                          
'----------------------------------------------------------------------*/

CREATE PROCEDURE dbo.SP_DeleteNurse
@vchNurseID VARCHAR(30)
AS
   DELETE FROM Nurse  WHERE   (NurseID = @vchNurseID)

GRANT EXEC ON dbo.SP_DeleteNurse TO hms_usr
GO

